unit FBuscaPapel_Viejo;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, Grids, XDBGrids, DB, ZAbstractRODataset, ZDataset,
  ExtCtrls, Buttons;

type
  TFormBusca_Papel_viejo = class(TForm)
    Label1: TLabel;
    pBuscar: TPanel;
    qPapeles_Alm: TZReadOnlyQuery;
    dsPapeles_Alm: TDataSource;
    eCodigo: TEdit;
    Grid: TXDBGrid;
    eTipo: TEdit;
    eColor: TEdit;
    eFamilia: TEdit;
    Label2: TLabel;
    Label3: TLabel;
    Label4: TLabel;
    Label5: TLabel;
    Label6: TLabel;
    pBotones: TPanel;
    BtnAceptar: TBitBtn;
    BtnCancelar: TBitBtn;
    Label8: TLabel;
    eGeneracion: TEdit;
    Label9: TLabel;
    eGramage: TEdit;
    Label11: TLabel;
    eAncho: TEdit;
    Label12: TLabel;
    eLargo: TEdit;
    procedure eCodigoChange(Sender: TObject);
    procedure BtnAceptarClick(Sender: TObject);
    procedure ePrecioKeyPress(Sender: TObject; var Key: Char);
    procedure FormActivate(Sender: TObject);
    procedure GridTitleClick(Column: TXColumn);
    procedure GridPaintColumnCell(Sender: TObject; const Rect: TRect;
      DataCol: Integer; Column: TXColumn; Highlight: Boolean;
      Selections: TSelections; var Color: TColor; Font: TFont;
      var Image: TPersistent);
    procedure FormDestroy(Sender: TObject);
    procedure GridDblClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    Id_Papel: Integer;
  end;

var
  FormBusca_Papel_viejo: TFormBusca_Papel_viejo;

implementation

uses ModDatos;

{$R *.dfm}

procedure TFormBusca_Papel_viejo.eCodigoChange(Sender: TObject);
var
  Asoc  ,
  Color ,
  StrSQL: String;

  Procedure SumaSQL( const Valor, Campo: String);
  begin
    if Valor <> '' then StrSQL := StrSQL + 'AND ' + Campo + ' LIKE ' + QuotedStr( '%' + Valor + '%') + ' ';
  end;

  Function Asociacion_Papel_Proveed: String;
  var
    Select : String;
  begin
    Select := 'SELECT * ' +
              'FROM TIPOS_PAPEL_PROVEEDOR ';
    MD.AbreQuery( md.Query, Select);
    Result := '';
    While not md.Query.Eof do
    begin
      if Result <> '' then Result := Result + ' OR ';
      Result := Result + 'MA.IDPROVEEDOR = ' + QuotedStr( md.Query.FieldByName( 'IDPROVEEDOR').AsString) + ' AND MA.IDTIPODEPAPEL = '+ QuotedStr( md.Query.FieldByName( 'IDTIPODEPAPEL').AsString)+ ' ';
      MD.Query.Next;
    end;
    if Result <> '' then Result := '(' + Result + ')';
  end;

begin
  StrSQL := '';
  SumaSQL( eCodigo.Text, 'MA.IDMATERIAL');
  SumaSQL( eTipo.Text, 'TP.DESCRIPCION');
  SumaSQL( eFamilia.Text, 'MA.FAMILIA');
  SumaSQL( eGeneracion.Text, 'GE.DESCRIPCION');
  SumaSQL( eGramage.Text, 'MA.GRAMAJE');
  SumaSQL( eAncho.Text, 'MA.ANCHO');
  SumaSQL( eLargo.Text, 'MA.LARGO');
  //Reemplazamos el primer AND por un Where
  StrSQL := StringReplace( StrSQL, 'AND', 'WHERE', []);

  Asoc := Asociacion_Papel_Proveed;
  if Asoc <> '' then
    if StrSQL = '' then StrSQL := 'WHERE ' + Asoc
                   else StrSQL := StrSQL + ' AND ' + Asoc;

  //El tema del color, se las trae, por las 3 tablas.
  if eColor.Text <> '' then
  begin
    if StrSQL = '' then Color := 'WHERE '
                   else Color := 'AND ';
    Color := Color +
             '(( MA.FAMILIA = ''Bobinas'' AND ( SELECT COUNT( *) FROM ALM_COLORES_BOB   WHERE IDCOLOR = MA.IDCOLOR AND DESCRIPCION LIKE ' + QuotedStr( '%' + eColor.Text + '%') + ') > 0) OR ' +
             ' ( MA.FAMILIA = ''Papel'' AND ( SELECT COUNT( *) FROM ALM_COLORES_PAPEL WHERE IDCOLOR = MA.IDCOLOR AND DESCRIPCION LIKE ' + QuotedStr( '%' + eColor.Text + '%') + ') > 0) OR ' +
             ' ( MA.FAMILIA = ''Sobres y Bolsas'' AND ( SELECT COUNT( *) FROM ALM_COLORES_SYB   WHERE IDCOLOR = MA.IDCOLOR AND DESCRIPCION LIKE ' + QuotedStr( '%' + eColor.Text + '%') + ') > 0)) '
  end
  else
    Color := '';

  StrSQL := 'SELECT MA.IDMATERIAL, MA.REFERENCIA, PR.RAZONSOCIAL PROVEEDOR, TP.DESCRIPCION TIPO_PAPEL, MA.LASER, GE.DESCRIPCION GENERACION, MA.GRAMAJE, TU.DESCRIPCION UNIDADES, MA.LARGO, MA.ANCHO, MA.FAMILIA, MA.PRECIOCOMPRA, ' +
                   'CASE MA.FAMILIA ' +
                     'WHEN ''Bobinas'' THEN ( SELECT DESCRIPCION FROM ALM_COLORES_BOB WHERE IDCOLOR = MA.IDCOLOR ) ' +
                     'WHEN ''Papel''   THEN ( SELECT DESCRIPCION FROM ALM_COLORES_PAPEL WHERE IDCOLOR = MA.IDCOLOR ) ' +
                     'WHEN ''Sobres y Bolsas'' THEN ( SELECT DESCRIPCION FROM ALM_COLORES_SYB WHERE IDCOLOR = MA.IDCOLOR ) ' +
                   'END COLOR ' +
            'FROM ALM_MATERIASPRIMAS MA ' +
                 'LEFT JOIN ALM_PROVEEDORES PR ON MA.IDPROVEEDOR = PR.IDPROVEEDOR ' +
                 'LEFT JOIN ALM_TIPOSDEPAPEL TP ON MA.IDTIPODEPAPEL = TP.IDTIPODEPAPEL ' +
                 'LEFT JOIN ALM_GENERACIONES GE ON MA.IDGENERACION = GE.IDGENERACION ' +
                 'LEFT JOIN ALM_TIPOSDEUNIDAD TU ON MA.IDTIPOUNIDAD = TU.IDTIPODEUNIDAD ' +
            StrSQL + Color +
            'ORDER BY MA.IDMATERIAL';
  md.AbreQuery( qPapeles_Alm, StrSQL);

  if Id_Papel > 0 then
    qPapeles_Alm.Locate( 'IDMATERIAL', Id_papel, []);
end;

procedure TFormBusca_Papel_viejo.BtnAceptarClick(Sender: TObject);
begin
  if qPapeles_Alm.IsEmpty then ModalResult := mrCancel
                          else id_Papel    := qPapeles_Alm.fieldByName( 'IDMATERIAL').AsInteger;
end;

procedure TFormBusca_Papel_viejo.ePrecioKeyPress(Sender: TObject; var Key: Char);
begin
  If Key = '.' then Key := ','
end;

procedure TFormBusca_Papel_viejo.FormActivate(Sender: TObject);
begin
  eCodigoChange( Sender);
end;

procedure TFormBusca_Papel_viejo.GridTitleClick(Column: TXColumn);
begin
  Grid.OrderFields := Column.FieldName
end;

procedure TFormBusca_Papel_viejo.GridPaintColumnCell(Sender: TObject;
  const Rect: TRect; DataCol: Integer; Column: TXColumn;
  Highlight: Boolean; Selections: TSelections; var Color: TColor;
  Font: TFont; var Image: TPersistent);
begin
  if qPapeles_Alm.FieldByName( 'IDMATERIAL').AsInteger = Id_Papel then Font.Style := [ fsBold]
end;

procedure TFormBusca_Papel_viejo.FormDestroy(Sender: TObject);
begin
  qPapeles_Alm.Close;
end;

procedure TFormBusca_Papel_viejo.GridDblClick(Sender: TObject);
begin
  BtnAceptarClick( Sender);
  ModalResult := mrOk;
end;

end.
